
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 06/11/2012 11:22:26
-- Generated from EDMX file: D:\Projects\CMP\CMP.DBFirst\trunk\src\Entity\Admin\DBEntities.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [HisPlatform];
GO
IF SCHEMA_ID(N'admin') IS NULL EXECUTE(N'CREATE SCHEMA [admin]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------


-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------


-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Dictionaries'
CREATE TABLE [admin].[Dictionaries] (
    [Key] nvarchar(50)  NOT NULL,
    [Comment] nvarchar(255)  NULL,
    [IsSysRequired] bit  NOT NULL
);
GO

-- Creating table 'DictionaryDetails'
CREATE TABLE [admin].[DictionaryDetails] (
    [ID] char(36)  NOT NULL,
    [DictionaryKey] nvarchar(50)  NOT NULL,
    [Value] nvarchar(50)  NOT NULL,
    [Description] nvarchar(255)  NULL,
    [OrderIndex] int  NULL,
    [Enable] bit  NOT NULL
);
GO

-- Creating table 'Menus'
CREATE TABLE [admin].[Menus] (
    [ID] char(36)  NOT NULL,
    [MenuText] nvarchar(50)  NOT NULL,
    [Area] nvarchar(50)  NULL,
    [Controller] nvarchar(50)  NULL,
    [Action] nvarchar(50)  NULL,
    [Enable] bit  NOT NULL,
    [ParentID] char(36)  NULL,
    [OrderIndex] int  NULL,
    [CheckRight] bit  NOT NULL,
    [Target] nvarchar(50)  NULL,
    [Parms] nvarchar(50)  NULL,
    [SmallImageName] nvarchar(50)  NULL,
    [LargeImageName] nvarchar(50)  NULL
);
GO

-- Creating table 'Messages'
CREATE TABLE [admin].[Messages] (
    [ID] char(36)  NOT NULL,
    [Sender_ID] char(36)  NULL,
    [Content] nvarchar(4000)  NOT NULL,
    [RefId] char(36)  NULL,
    [SendTime] datetime  NOT NULL,
    [MessageType] nvarchar(50)  NOT NULL
);
GO

-- Creating table 'Notifications'
CREATE TABLE [admin].[Notifications] (
    [ID] char(36)  NOT NULL,
    [User_ID] char(36)  NOT NULL,
    [Content] nvarchar(255)  NOT NULL
);
GO

-- Creating table 'Roles'
CREATE TABLE [admin].[Roles] (
    [ID] char(36)  NOT NULL,
    [RoleName] nvarchar(50)  NULL,
    [RoleMemo] nvarchar(50)  NULL
);
GO

-- Creating table 'UserLogs'
CREATE TABLE [admin].[UserLogs] (
    [ID] char(36)  NOT NULL,
    [Area] nvarchar(255)  NULL,
    [Controller] nvarchar(255)  NULL,
    [Action] nvarchar(255)  NULL,
    [Parms] nvarchar(max)  NULL,
    [User] nvarchar(50)  NULL,
    [RemoteIP] nchar(10)  NOT NULL,
    [TimeSpan] float  NULL,
    [AddTime] datetime  NULL
);
GO

-- Creating table 'UserMatchMessages'
CREATE TABLE [admin].[UserMatchMessages] (
    [ID] char(36)  NOT NULL,
    [Message_ID] char(36)  NOT NULL,
    [Receiver_ID] char(36)  NOT NULL,
    [IsRead] bit  NOT NULL,
    [ReadTime] datetime  NOT NULL
);
GO

-- Creating table 'Users'
CREATE TABLE [admin].[Users] (
    [ID] char(36)  NOT NULL,
    [UserName] nvarchar(50)  NOT NULL,
    [Password] nvarchar(50)  NOT NULL,
    [Email] nvarchar(50)  NULL,
    [MobilePIN] nvarchar(16)  NULL,
    [PasswordQuestion] nvarchar(256)  NULL,
    [PasswordAnswer] nvarchar(256)  NULL,
    [IslockedOut] bit  NOT NULL,
    [CreateDate] datetime  NULL,
    [LastLoginDate] datetime  NULL,
    [LastPasswordChangedDate] datetime  NULL,
    [LastLockoutDate] datetime  NULL,
    [Comment] nvarchar(max)  NULL
);
GO

-- Creating table 'InquiryParameters'
CREATE TABLE [admin].[InquiryParameters] (
    [ID] char(36)  NOT NULL,
    [Name] nvarchar(50)  NOT NULL,
    [ViewPath] nvarchar(50)  NOT NULL,
    [Enable] bit  NOT NULL,
    [Memo] nvarchar(255)  NULL,
    [OrderIndex] int  NOT NULL
);
GO

-- Creating table 'ParameterRules'
CREATE TABLE [admin].[ParameterRules] (
    [ID] char(36)  NOT NULL,
    [RulePath] nvarchar(255)  NOT NULL,
    [RoleLeave] int  NOT NULL,
    [Memo] nvarchar(50)  NULL
);
GO

-- Creating table 'RoleMatchMenu'
CREATE TABLE [admin].[RoleMatchMenu] (
    [Menus_ID] char(36)  NOT NULL,
    [Roles_ID] char(36)  NOT NULL
);
GO

-- Creating table 'UserMatchRole'
CREATE TABLE [admin].[UserMatchRole] (
    [Roles_ID] char(36)  NOT NULL,
    [Users_ID] char(36)  NOT NULL
);
GO

-- Creating table 'InquiryParameterMatchAccessRule'
CREATE TABLE [admin].[InquiryParameterMatchAccessRule] (
    [InquiryParameters_ID] char(36)  NOT NULL,
    [ParameterRules_ID] char(36)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Key] in table 'Dictionaries'
ALTER TABLE [admin].[Dictionaries]
ADD CONSTRAINT [PK_Dictionaries]
    PRIMARY KEY CLUSTERED ([Key] ASC);
GO

-- Creating primary key on [ID] in table 'DictionaryDetails'
ALTER TABLE [admin].[DictionaryDetails]
ADD CONSTRAINT [PK_DictionaryDetails]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Menus'
ALTER TABLE [admin].[Menus]
ADD CONSTRAINT [PK_Menus]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Messages'
ALTER TABLE [admin].[Messages]
ADD CONSTRAINT [PK_Messages]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Notifications'
ALTER TABLE [admin].[Notifications]
ADD CONSTRAINT [PK_Notifications]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Roles'
ALTER TABLE [admin].[Roles]
ADD CONSTRAINT [PK_Roles]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'UserLogs'
ALTER TABLE [admin].[UserLogs]
ADD CONSTRAINT [PK_UserLogs]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'UserMatchMessages'
ALTER TABLE [admin].[UserMatchMessages]
ADD CONSTRAINT [PK_UserMatchMessages]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Users'
ALTER TABLE [admin].[Users]
ADD CONSTRAINT [PK_Users]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'InquiryParameters'
ALTER TABLE [admin].[InquiryParameters]
ADD CONSTRAINT [PK_InquiryParameters]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'ParameterRules'
ALTER TABLE [admin].[ParameterRules]
ADD CONSTRAINT [PK_ParameterRules]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [Menus_ID], [Roles_ID] in table 'RoleMatchMenu'
ALTER TABLE [admin].[RoleMatchMenu]
ADD CONSTRAINT [PK_RoleMatchMenu]
    PRIMARY KEY NONCLUSTERED ([Menus_ID], [Roles_ID] ASC);
GO

-- Creating primary key on [Roles_ID], [Users_ID] in table 'UserMatchRole'
ALTER TABLE [admin].[UserMatchRole]
ADD CONSTRAINT [PK_UserMatchRole]
    PRIMARY KEY NONCLUSTERED ([Roles_ID], [Users_ID] ASC);
GO

-- Creating primary key on [InquiryParameters_ID], [ParameterRules_ID] in table 'InquiryParameterMatchAccessRule'
ALTER TABLE [admin].[InquiryParameterMatchAccessRule]
ADD CONSTRAINT [PK_InquiryParameterMatchAccessRule]
    PRIMARY KEY NONCLUSTERED ([InquiryParameters_ID], [ParameterRules_ID] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [DictionaryKey] in table 'DictionaryDetails'
ALTER TABLE [admin].[DictionaryDetails]
ADD CONSTRAINT [FK_DictionaryDetail_Dictionary]
    FOREIGN KEY ([DictionaryKey])
    REFERENCES [admin].[Dictionaries]
        ([Key])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_DictionaryDetail_Dictionary'
CREATE INDEX [IX_FK_DictionaryDetail_Dictionary]
ON [admin].[DictionaryDetails]
    ([DictionaryKey]);
GO

-- Creating foreign key on [ParentID] in table 'Menus'
ALTER TABLE [admin].[Menus]
ADD CONSTRAINT [FK_Menu_Menu]
    FOREIGN KEY ([ParentID])
    REFERENCES [admin].[Menus]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Menu_Menu'
CREATE INDEX [IX_FK_Menu_Menu]
ON [admin].[Menus]
    ([ParentID]);
GO

-- Creating foreign key on [RefId] in table 'Messages'
ALTER TABLE [admin].[Messages]
ADD CONSTRAINT [FK_Message_Message]
    FOREIGN KEY ([RefId])
    REFERENCES [admin].[Messages]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Message_Message'
CREATE INDEX [IX_FK_Message_Message]
ON [admin].[Messages]
    ([RefId]);
GO

-- Creating foreign key on [Message_ID] in table 'UserMatchMessages'
ALTER TABLE [admin].[UserMatchMessages]
ADD CONSTRAINT [FK_UserMatchMessage_Message]
    FOREIGN KEY ([Message_ID])
    REFERENCES [admin].[Messages]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserMatchMessage_Message'
CREATE INDEX [IX_FK_UserMatchMessage_Message]
ON [admin].[UserMatchMessages]
    ([Message_ID]);
GO

-- Creating foreign key on [User_ID] in table 'Notifications'
ALTER TABLE [admin].[Notifications]
ADD CONSTRAINT [FK_Notification_User]
    FOREIGN KEY ([User_ID])
    REFERENCES [admin].[Users]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Notification_User'
CREATE INDEX [IX_FK_Notification_User]
ON [admin].[Notifications]
    ([User_ID]);
GO

-- Creating foreign key on [Receiver_ID] in table 'UserMatchMessages'
ALTER TABLE [admin].[UserMatchMessages]
ADD CONSTRAINT [FK_UserMatchMessage_User]
    FOREIGN KEY ([Receiver_ID])
    REFERENCES [admin].[Users]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserMatchMessage_User'
CREATE INDEX [IX_FK_UserMatchMessage_User]
ON [admin].[UserMatchMessages]
    ([Receiver_ID]);
GO

-- Creating foreign key on [Menus_ID] in table 'RoleMatchMenu'
ALTER TABLE [admin].[RoleMatchMenu]
ADD CONSTRAINT [FK_RoleMatchMenu_Menus]
    FOREIGN KEY ([Menus_ID])
    REFERENCES [admin].[Menus]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Roles_ID] in table 'RoleMatchMenu'
ALTER TABLE [admin].[RoleMatchMenu]
ADD CONSTRAINT [FK_RoleMatchMenu_Roles]
    FOREIGN KEY ([Roles_ID])
    REFERENCES [admin].[Roles]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_RoleMatchMenu_Roles'
CREATE INDEX [IX_FK_RoleMatchMenu_Roles]
ON [admin].[RoleMatchMenu]
    ([Roles_ID]);
GO

-- Creating foreign key on [Roles_ID] in table 'UserMatchRole'
ALTER TABLE [admin].[UserMatchRole]
ADD CONSTRAINT [FK_UserMatchRole_Roles]
    FOREIGN KEY ([Roles_ID])
    REFERENCES [admin].[Roles]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Users_ID] in table 'UserMatchRole'
ALTER TABLE [admin].[UserMatchRole]
ADD CONSTRAINT [FK_UserMatchRole_Users]
    FOREIGN KEY ([Users_ID])
    REFERENCES [admin].[Users]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserMatchRole_Users'
CREATE INDEX [IX_FK_UserMatchRole_Users]
ON [admin].[UserMatchRole]
    ([Users_ID]);
GO

-- Creating foreign key on [InquiryParameters_ID] in table 'InquiryParameterMatchAccessRule'
ALTER TABLE [admin].[InquiryParameterMatchAccessRule]
ADD CONSTRAINT [FK_InquiryParameterMatchAccessRule_InquiryParameters]
    FOREIGN KEY ([InquiryParameters_ID])
    REFERENCES [admin].[InquiryParameters]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [ParameterRules_ID] in table 'InquiryParameterMatchAccessRule'
ALTER TABLE [admin].[InquiryParameterMatchAccessRule]
ADD CONSTRAINT [FK_InquiryParameterMatchAccessRule_ParameterRules]
    FOREIGN KEY ([ParameterRules_ID])
    REFERENCES [admin].[ParameterRules]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_InquiryParameterMatchAccessRule_ParameterRules'
CREATE INDEX [IX_FK_InquiryParameterMatchAccessRule_ParameterRules]
ON [admin].[InquiryParameterMatchAccessRule]
    ([ParameterRules_ID]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------